﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Data.Linq;
using System.Data.Linq.Mapping;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using System.Transactions;
using Aspect;
using iMobileS.Common;

namespace iMobileS.Models
{
    public interface IDatabase
    {
        bool UpdateObject<T>(string storeProcedure, T obj) where T : class;
        bool InsertObject<T>(string storeProcedure, T obj) where T : class;
        bool DeleteObject<T>(string storeProcedure, T obj) where T : class;
        bool InsertListObject<T>(string storeProcedure, List<T> objectList) where T : class;
        bool UpdateListObject<T>(string storeProcedure, List<T> objectList) where T : class;

        /// <summary>
        /// Execute an store procedure against the database to get one or more record set
        /// </summary>
        /// <typeparam name="TResultType"></typeparam>
        /// <param name="storeProcedure"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        TResultType GetSingleRecord<TResultType>(string storeProcedure, params object[] parameters);

        /// <summary>
        /// Execute a store procedure against the database to get multiple record set.
        /// </summary>
        /// <typeparam name="TResultType"></typeparam>
        /// <param name="storeProcedure"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        IEnumerable<TResultType> GetManyRecords<TResultType>(string storeProcedure, params object[] parameters) where TResultType : class;

        /// <summary>
        /// Execute multiple query (width one store procedure) in single connection. 
        /// This's useful when execute one SP several times with different values of input parameters
        /// </summary>
        /// <typeparam name="TResultType"></typeparam>
        /// <param name="storeProcedure"></param>
        /// <param name="paramsList"></param>
        /// <returns></returns>
        IEnumerable<TResultType> GetManyRecordsWithPrams<TResultType>(string storeProcedure, List<List<object>> paramsList) where TResultType : class;

        /// <summary>
        /// Execute a store procedure against the database to get multiple table like DataSet
        /// </summary>
        /// <param name="storeProcedure"></param>
        /// <param name="parameters"></param>
        /// <returns>IMultipleResults type </returns>
        IMultipleResults GetManyTables(string storeProcedure, params object[] parameters);

        /// <summary>
        /// Execute multiple query in single connection. Very handy for insert, update, delete multiple record.
        /// For each manipulation, this method accepts just delete, insert, or update query
        /// </summary>
        /// <param name="storeProcedure">Store Procedure name</param>
        /// <param name="paramsList">
        /// The list of params which pass to each query. 
        /// The order of params of each item in list must match with the order of parameters of Store procedure</param>
        /// <returns>The number of row affected</returns>
        int ExecuteNoneBatchQuery(string storeProcedure, List<List<object>> paramsList);

    }

    public abstract class Database : DataContext, IDatabase
    {
        private static MappingSource mappingSource = new AttributeMappingSource();
        public Database(string connectionString) :
            base(connectionString, mappingSource)
        {
        }
        #region Logger
        private ILogger _logger;
        public ILogger Logger
        {
            get { return this._logger; }
            set { this._logger = value; }
        }
        #endregion Logger

        #region Private methods
        private Type[] GetPropertyTypes<T>(T obj)
            where T : class
        {
            Type type = obj.GetType();
            return type.GetProperties().Where(p =>
                    !p.PropertyType.IsClass
                    || p.PropertyType.IsValueType
                    || p.PropertyType == typeof(String)
                ).Select(p => p.PropertyType).ToArray();
        }

        private void ValidateName(string name)
        {
            if (string.IsNullOrEmpty(name))
                throw new Exception("Procedure name must be not empty");
            if (name.ToLower().StartsWith("exec"))
                throw new Exception("Illegal of store procedure");
            if (name.Trim().Split(' ').Length != 1)
                throw new Exception("Illegal of store procedure");
        }
        private string BuildTextCommand<T>(string storeProc, T obj)
            where T : class
        {
            this.ValidateName(storeProc);

            Type[] types = this.GetPropertyTypes<T>(obj);
            int length = types.Length;
            string command = string.Format("EXEC {0} ", storeProc);
            for (int i = 0; i < length; i++)
            {
                if (i < length - 1)
                    command += "{" + i.ToString() + "},";
                if (i == length - 1)
                    command += "{" + i.ToString() + "}";
            }

            return command;
        }
        private object[] GetPropertyValues<T>(T obj)
           where T : class
        {
            Type type = obj.GetType();

            PropertyInfo[] properties = type.GetProperties();

            List<object> objList = new List<object>();
            properties.Each(p =>
            {
                object[] customAttrs = p.GetCustomAttributes(typeof(NonDBFieldAttribute), true);
                if (customAttrs == null || customAttrs.Length <= 0)
                {
                    if (!p.PropertyType.IsClass ||
                        p.PropertyType.IsValueType ||
                        p.PropertyType == typeof(String))
                    {
                        object o = p.GetValue(obj, null);
                        objList.Add(o);
                    }
                }
            });
            return objList.ToArray();
        }
        private string BuildTextCommand(string storeProc, params object[] parameters)
        {
            this.ValidateName(storeProc);

            int length = parameters.Length;
            string command = string.Format("EXEC {0} ", storeProc);
            int index = 0;
            for (int i = 0; i < length; i++)
            {
                if ((parameters[i] == null || parameters[i] is System.DBNull))
                {
                    if (i < length - 1)
                        command += "NULL,";
                    if (i == length - 1)
                        command += "NULL";
                }
                else
                {
                    //string f = "{0}";
                    //if (parameters[i].GetType() == typeof(string))
                    //    f = "N'{0}'"; // Fixed problem when insert unicode characters

                    if (i < length - 1)
                        command += "{" + index.ToString() + "},";
                    if (i == length - 1)
                        command += "{" + index.ToString() + "}";
                    index++;
                }
            }

            return command;
        }

        private string BuildBatchCommand(string storeProc, List<List<object>> paramList, List<object> outputParams)
        {
            this.ValidateName(storeProc);

            string exeCommand = string.Format("EXEC {0} ", storeProc);
            string command = string.Empty;

            System.Text.StringBuilder commandBuilder = new System.Text.StringBuilder();
            int index = 0,
                i = 0,
                length = 0;

            foreach (IEnumerable<object> itemList in paramList)
            {
                length = itemList.Count();
                i = 0;
                command = string.Empty;

                foreach (object item in itemList)
                {
                    if (item == null || item is System.DBNull)
                    {
                        if (i < length - 1)
                            command += "NULL,";
                        if (i == length - 1)
                            command += "NULL";
                    }
                    else
                    {
                        if (i < length - 1)
                            command += "{" + index.ToString() + "},";
                        if (i == length - 1)
                            command += "{" + index.ToString() + "}";

                        outputParams.Add(item);

                        index++;
                    }
                    i++;
                }

                commandBuilder.AppendLine(exeCommand + command);
            }

            return commandBuilder.ToString();
        }

        private object[] EnsureParams(object[] parameters)
        {
            List<object> objList = new List<object>();
            foreach (var p in parameters)
            {
                if ((p != null && !(p is System.DBNull)))
                {
                    objList.Add(p);
                }
            }

            return objList.ToArray();
        }
        #endregion Private methods

        #region IDatabase Members
        public TResultType GetSingleRecord<TResultType>(string storeProcedure, params object[] parameters)
        {
            return this.ExecuteQuery<TResultType>(BuildTextCommand(storeProcedure, parameters), EnsureParams(parameters)).FirstOrDefault();
        }

        public IEnumerable<TResultType> GetManyRecords<TResultType>(string storeProcedure, params object[] parameters) where TResultType : class
        {
            return this.ExecuteQuery<TResultType>(BuildTextCommand(storeProcedure, parameters), EnsureParams(parameters));
        }

        public IEnumerable<TResultType> GetManyRecordsWithPrams<TResultType>(string storeProcedure, List<List<object>> paramsList) where TResultType : class
        {
            DbConnection connection = new SqlConnection(this.Connection.ConnectionString);
            connection.Open();
            DbCommand cmd = connection.CreateCommand();
            List<List<object>> paras = new List<List<object>>(paramsList.Count);
            List<object> paramsOutput = new List<object>();
            int numberOfQuery = 0;

            foreach (List<object> listPara in paramsList)
            {
                ++numberOfQuery;

                List<object> newParam = new List<object>();
                foreach (object pr in listPara)
                {

                    if (pr != null && pr.GetType() == typeof(String))
                    {
                        newParam.Add(string.Format(System.Globalization.CultureInfo.CreateSpecificCulture("en-US"), "N'{0}'", ((String)pr).Replace("'", "''")));
                    }
                    else if (pr != null && pr.GetType() == typeof(DateTime))
                    {
                        newParam.Add(string.Format(System.Globalization.CultureInfo.CreateSpecificCulture("en-US"), "'{0:G}'", pr));
                    }
                    else
                        newParam.Add(pr);
                }
                paras.Add(newParam);
            }
            string command = this.BuildBatchCommand(storeProcedure, paras, paramsOutput);
            cmd.CommandText = string.Format(command, paramsOutput.ToArray());
            DbDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            IMultipleResults multiResults = this.Translate(reader);

            if (multiResults != null)
            {
                List<TResultType> results = new List<TResultType>();

                for (int i = 0; i < numberOfQuery; i++)
                {
                    IEnumerable<TResultType> lst = multiResults.GetResult<TResultType>();
                    if (null != lst)
                        results.AddRange(lst);
                }

                return results;
            }
            return null;
        }

        public IMultipleResults GetManyTables(string storeProcedure, params object[] parameters)
        {
            DbConnection connection = new SqlConnection(this.Connection.ConnectionString);
            connection.Open();
            DbCommand cmd = connection.CreateCommand();
            object[] param = new object[parameters.Length];
            int i = 0;
            foreach (object pr in parameters)
            {
                if (pr.GetType() == typeof(String))
                {
                    param[i] = string.Format(System.Globalization.CultureInfo.CreateSpecificCulture("en-US"), "N'{0}'", ((String)pr).Replace("'", "''"));
                }
                else if (pr.GetType() == typeof(DateTime))
                {
                    param[i] = string.Format(System.Globalization.CultureInfo.CreateSpecificCulture("en-US"), "'{0:G}'", pr);
                }
                else
                    param[i] = pr;

                ++i;
            }
            cmd.CommandText = string.Format(BuildTextCommand(storeProcedure, parameters), param);
            DbDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            return this.Translate(reader);
        }

        public bool UpdateObject<T>(string storeProcedure, T obj) where T : class
        {
            return AspectF.Object
                .HowLong(this.Logger,
                    string.Format("[{0}]: Execute \"{1}\"", DateTime.Now.ToShortTimeString(), storeProcedure),
                    "In {0} miliseconds ({1} seconds)"
                )
                .TrapLogThrow(this.Logger)
                .Return<bool>(() =>
                {
                    object[] propertiesVals = GetPropertyValues<T>(obj);
                    return this.ExecuteCommand(BuildTextCommand(storeProcedure, propertiesVals), EnsureParams(propertiesVals)) > 0;
                });
        }

        public bool InsertObject<T>(string storeProcedure, T obj) where T : class
        {
            //return AspectF.Object
            //    .HowLong(this.Logger,
            //        string.Format("[{0}]: Execute \"{1}\"", DateTime.Now.ToShortTimeString(), storeProcedure),
            //        "In {0} miliseconds ({1} seconds)"
            //    )
            //    .TrapLogThrow(this.Logger)
            //    .Return<bool>(() =>
            //    {
            object[] propertiesVals = GetPropertyValues<T>(obj);
            return this.ExecuteCommand(BuildTextCommand(storeProcedure, propertiesVals), EnsureParams(propertiesVals)) > 0;
            //});
        }

        public bool DeleteObject<T>(string storeProcedure, T obj) where T : class
        {
            return AspectF.Object
                .HowLong(this.Logger,
                    string.Format("[{0}]: Execute \"{1}\"", DateTime.Now.ToShortTimeString(), storeProcedure),
                    "In {0} miliseconds ({1} seconds)"
                )
                .TrapLogThrow(this.Logger)
                .Return<bool>(() =>
                {
                    object[] propertiesVals = GetPropertyValues<T>(obj);
                    return this.ExecuteCommand(BuildTextCommand(storeProcedure, propertiesVals), EnsureParams(propertiesVals)) > 0;
                });
        }

        public bool InsertListObject<T>(string storeProcedure, List<T> objectList) where T : class
        {
            if (objectList == null || objectList.Count <= 0)
                return false;
            List<List<object>> paramsList = new List<List<object>>();
            foreach (T item in objectList)
            {
                List<object> param = new List<object>();
                param.AddRange(GetPropertyValues<T>(item));
                paramsList.Add(param);
            }
            return this.ExecuteNoneBatchQuery(storeProcedure, paramsList) > 0;
        }

        public bool UpdateListObject<T>(string storeProcedure, List<T> objectList) where T : class
        {
            if (objectList == null || objectList.Count <= 0)
                return false;
            List<List<object>> paramsList = new List<List<object>>();
            foreach (T item in objectList)
            {
                List<object> param = new List<object>();
                param.AddRange(GetPropertyValues<T>(item));
                paramsList.Add(param);
            }
            return this.ExecuteNoneBatchQuery(storeProcedure, paramsList) > 0;
        }

        public int ExecuteNoneBatchQuery(string storeProcedure, List<List<object>> paramsList)
        {
            if (paramsList != null)
            {
                using (TransactionScope trancScope = new TransactionScope(TransactionScopeOption.Required))
                {
                    int affectedCount = 0;
                    List<object> paramsOutput = new List<object>();
                    affectedCount = this.ExecuteCommand(this.BuildBatchCommand(storeProcedure, paramsList, paramsOutput), paramsOutput.ToArray());
                    if (affectedCount > 0)
                        trancScope.Complete();
                    return affectedCount;
                }
            }

            return 0;
        }
        #endregion
    }

    public partial class iMobileDatabase : Database
    {
        public iMobileDatabase()
            : base(ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString)
        {
        }

        public iMobileDatabase(string connectionString)
            : base(connectionString)
        {
        }
    }
}